package com.springboot.study.controller;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.http.ResponseEntity;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import com.springboot.study.pojo.UserInfo;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.web.bind.annotation.*;
import java.sql.*;
import java.time.LocalDateTime;
import java.util.List;

@RestController
@RequestMapping("/jdbc")
public class JDBCTestController {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    // RowMapper 用于把查询结果映射成 UserInfo 对象
    private final RowMapper<UserInfo> userRowMapper = (rs, rowNum) -> {
        UserInfo user = new UserInfo();
        user.setId(rs.getLong("id"));
        user.setUsername(rs.getString("username"));
        user.setPassword(rs.getString("password"));
        user.setEmail(rs.getString("email"));
        user.setPhone(rs.getString("phone"));
        Timestamp createdTs = rs.getTimestamp("created_time");
        if (createdTs != null) {
            user.setCreatedTime(createdTs.toLocalDateTime());
        }
        Timestamp updatedTs = rs.getTimestamp("updated_time");
        if (updatedTs != null) {
            user.setUpdatedTime(updatedTs.toLocalDateTime());
        }
        return user;
    };

    // 查询所有用户
    @GetMapping("/users")
    public List<UserInfo> getAllUsers() {
        String sql = "SELECT * FROM user_info";
        return jdbcTemplate.query(sql, userRowMapper);
    }

    // 根据id查询用户
    @GetMapping("/users/{id}")
    public ResponseEntity<UserInfo> getUserById(@PathVariable Long id) {
        String sql = "SELECT * FROM user_info WHERE id = ?";
        List<UserInfo> users = jdbcTemplate.query(sql, userRowMapper, id);
        if (users.isEmpty()) {
            return ResponseEntity.notFound().build();
        }
        return ResponseEntity.ok(users.get(0));
    }

    // 新增用户
    @PostMapping("/deteusers")
    public ResponseEntity<String> addUser(@RequestBody UserInfo user) {
        String sql = "INSERT INTO user_info (username, password, email, phone, created_time, updated_time) " +
                "VALUES (?, ?, ?, ?, ?, ?)";

        LocalDateTime now = LocalDateTime.now();
        int result = jdbcTemplate.update(sql,
                user.getUsername(),
                user.getPassword(),
                user.getEmail(),
                user.getPhone(),
                Timestamp.valueOf(now),
                Timestamp.valueOf(now));

        if (result > 0) {
            return ResponseEntity.ok("User added successfully");
        }
        return ResponseEntity.status(500).body("Failed to add user");
    }

    // 更新用户（除id外所有字段都可以更新）
    @PutMapping("/updateusers/{id}")
    public ResponseEntity<String> updateUser(@PathVariable Long id, @RequestBody UserInfo user) {
        String sql = "UPDATE user_info SET username = ?, password = ?, email = ?, phone = ?, updated_time = ? WHERE id = ?";
        int result = jdbcTemplate.update(sql,
                user.getUsername(),
                user.getPassword(),
                user.getEmail(),
                user.getPhone(),
                Timestamp.valueOf(LocalDateTime.now()),
                id);
        if (result > 0) {
            return ResponseEntity.ok("User updated successfully");
        }
        return ResponseEntity.notFound().build();
    }

    // 删除用户
    @DeleteMapping("/deteusers/{id}")
    public ResponseEntity<Void> deleteUser(@PathVariable Long id) {
        String sql = "DELETE FROM user_info WHERE id = ?";
        int result = jdbcTemplate.update(sql, id);
        if (result > 0) {
            return ResponseEntity.noContent().build();
        }
        return ResponseEntity.notFound().build();
    }

}
